﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

using BMS.Models;
using BMS.Utilities;

namespace BMS.Models.DataAccess
{
    public class ReportRepository
    {
        private DatabaseAccess db = new DatabaseAccess();

        public List<ReportModel> GetAllReportRecords()
        {
            List<ReportModel> allRecords = new List<ReportModel>();
            String query = "SELECT o.vc_fldServiceOrderNo as SONoOrRefNo, o.vc_fldLocation as Location, o.vc_fldCity as City"
                + ", o.vc_fldWorkOrderNo as WONo, if(q.vc_fldPONo IS NULL, '', q.vc_fldPONo) as PONo"
                + ", if(q.vc_fldQuotationNo IS NULL, '', q.vc_fldQuotationNo) as QuotNo, q.dt_fldCreate as QuotDate"
                + ", if(q.dt_fldWorkCompletion = '0001-01-01 00:00:00', NULL, q.dt_fldWorkCompletion) as WorkCompletionDate"
                + ", if(q.dc_fldTotalAmount IS NULL, '0.00', q.dc_fldTotalAmount) as QuotAmt"
                + ", if(Inv.InvNo IS NULL, '', Inv.InvNo) as InvNo, Inv.InvDate as InvDate, if(Inv.InvAmt IS NULL, '0.00', Inv.InvAmt) as InvAmt"
                + ", if(Inv.DNNo IS NULL, '', Inv.DNNo) as DNNo FROM "
                + Configuration.DatabaseName + ".order o LEFT OUTER JOIN "
                + Configuration.DatabaseName + ".quotation q ON q.vc_fldQuotationNo = o.vc_fldQuotationNo LEFT OUTER JOIN ("
                + "SELECT 	i.vc_fldInvoiceNo as InvNo,	i.dt_fldCreate as InvDate, iItem.vc_fldQuotationNo as QuotNo"
                + ", i.dc_fldTotalAmount as InvAmt, i.vc_fldDebitNoteNo as DNNo FROM "
                + Configuration.DatabaseName + ".invoice i INNER JOIN "
                + Configuration.DatabaseName + ".invoiceitem iItem ON iItem.vc_fldInvoiceNo = i.vc_fldInvoiceNo) AS Inv ON Inv.QuotNo = q.vc_fldQuotationNo";

            db.DBConnectionOn();

            try
            {
                MySqlCommand cmd = new MySqlCommand(query, db.connection);
                MySqlDataReader dataReader = cmd.ExecuteReader();

                while (dataReader.Read())
                {
                    ReportModel report = new ReportModel();

                    report.SONoOrRefNo = dataReader["SONoOrRefNo"] + "";
                    report.Location = dataReader["Location"] + "";
                    report.Region = dataReader["City"] + "";
                    report.WONo = dataReader["WONo"] + "";
                    report.PONo = dataReader["PONo"] + "";
                    report.QuotNo = dataReader["QuotNo"] + "";
                    report.QuotDate = ((dataReader["QuotDate"] + "") == "")? new DateTime() : DateTime.Parse(dataReader["QuotDate"] + "");
                    report.WorkCompletionDate = ((dataReader["WorkCompletionDate"] + "") == "") ? new DateTime() : DateTime.Parse(dataReader["WorkCompletionDate"] + "");
                    report.QuotAmt = decimal.Parse(dataReader["QuotAmt"] + "");
                    report.InvNo = dataReader["InvNo"] + "";
                    report.InvDate = ((dataReader["InvDate"] + "") == "") ? new DateTime() : DateTime.Parse(dataReader["InvDate"] + "");
                    report.InvAmt = decimal.Parse(dataReader["InvAmt"] + "");
                    report.DNNo = dataReader["DNNo"] + "";

                    allRecords.Add(report);
                }
            }
            catch (Exception e)
            {
                throw;
            }
            finally
            {
                db.DBConnectionOff();
            }
            return allRecords;
        }

        public List<ReportModel> GetExistingRecordsFromSources(List<ReportModel> srcA, List<ReportModel> srcB)
        {
            List<ReportModel> foundRecordsList = new List<ReportModel>();

            foreach(ReportModel recordA in srcA)
            {
                foreach(ReportModel recordB in srcB)
                {
                    if(recordB.SONoOrRefNo == recordA.SONoOrRefNo)
                    {
                        foundRecordsList.Add(recordA);
                        break;
                    }
                }
            }

            return foundRecordsList;
        }

        public List<ReportModel> GetNewRecordsFromSources(List<ReportModel> srcA, List<ReportModel> srcB)
        {
            List<ReportModel> foundRecordsList = new List<ReportModel>();

            foreach (ReportModel recordA in srcA)
            {
                bool isFound = false;

                foreach (ReportModel recordB in srcB)
                {
                    if (recordB.SONoOrRefNo == recordA.SONoOrRefNo)
                    {
                        isFound = false;
                        break;
                    }
                    else
                    {
                        isFound = true;
                    }
                }

                if (isFound == true)
                    foundRecordsList.Add(recordA);
            }

            return foundRecordsList;
        }

        public ReportModel GetFoundRecord(ReportModel model, List<ReportModel> modelList)
        {
            ReportModel foundModel = new ReportModel();

            foreach(ReportModel srcModel in modelList)
            {
                if(srcModel.SONoOrRefNo == model.SONoOrRefNo)
                {
                    foundModel = srcModel;
                    break;
                }
            }

            return foundModel;
        }

        public List<ReportModel> GetUpdatedRecordsFromSources(List<ReportModel> srcA, List<ReportModel> srcB)
        {
            List<ReportModel> foundRecordsList = new List<ReportModel>();
            
            foreach(ReportModel srcModel in srcA)
            {
                ReportModel foundRecord = GetFoundRecord(srcModel, srcB);

                if(srcModel.WONo != foundRecord.WONo || srcModel.PONo != foundRecord.PONo || srcModel.QuotNo != foundRecord.QuotNo
                    || srcModel.QuotDate != foundRecord.QuotDate || srcModel.WorkCompletionDate != foundRecord.WorkCompletionDate
                    || srcModel.QuotAmt != foundRecord.QuotAmt || srcModel.InvNo != foundRecord.InvNo || srcModel.InvDate != foundRecord.InvDate
                    || srcModel.InvAmt != foundRecord.InvAmt || srcModel.DNNo != foundRecord.DNNo)
                {
                    foundRecord.WONo = srcModel.WONo;
                    foundRecord.PONo = srcModel.PONo;
                    foundRecord.QuotNo = srcModel.QuotNo;
                    foundRecord.QuotDate = srcModel.QuotDate;
                    foundRecord.WorkCompletionDate = srcModel.WorkCompletionDate;
                    foundRecord.QuotAmt = srcModel.QuotAmt;
                    foundRecord.InvNo = srcModel.InvNo;
                    foundRecord.InvDate = srcModel.InvDate;
                    foundRecord.InvAmt = srcModel.InvAmt;
                    foundRecord.DNNo = srcModel.DNNo;

                    foundRecordsList.Add(foundRecord);
                }

            }

            return foundRecordsList;
        }
    }
}